﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.IO;
using BrendanGrant.Helpers.FileAssociation;
using System.Diagnostics;

namespace Kalitte.TTVys.CvsImporter
{
    public partial class FmMain : Form
    {
        public FmMain()
        {
            InitializeComponent();
        }

        public FmMain(string fileName)
            : this()
        {
            if (!string.IsNullOrEmpty(fileName))
            {
                Show();
                Refresh();
                if (string.IsNullOrEmpty(Path.GetExtension(fileName)))
                    fileName = fileName + ".csv";
                ctlFileName.Text = fileName;
                //if (File.Exists(ctlFileName.Text))
                //{
                //    Export(ctlFileName.Text);
                //    Close();
                //}
            }
        }

        public void ImportCSV(string importFileName, Microsoft.Office.Interop.Excel.Worksheet destinationSheet,
                     Microsoft.Office.Interop.Excel.Range destinationRange, int[] columnDataTypes, bool autoFitColumns, int startRow)
        {
            Enabled = false;
            try
            {
                var qtable = destinationSheet.QueryTables.Add(
                    "TEXT;" + Path.GetFullPath(importFileName),
                destinationRange, Type.Missing);
                qtable.Name = Path.GetFileNameWithoutExtension(importFileName);
                qtable.FieldNames = true;
                qtable.RowNumbers = false;
                qtable.FillAdjacentFormulas = false;
                qtable.PreserveFormatting = true;
                qtable.RefreshOnFileOpen = false;
                qtable.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
                qtable.SavePassword = false;
                qtable.SaveData = true;
                qtable.AdjustColumnWidth = true;
                qtable.RefreshPeriod = 0;
                qtable.TextFilePromptOnRefresh = false;
                qtable.TextFilePlatform = 65001;
                qtable.TextFileStartRow = startRow;
                qtable.TextFileParseType = XlTextParsingType.xlDelimited;
                qtable.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote;
                qtable.TextFileConsecutiveDelimiter = false;
                qtable.TextFileTabDelimiter = false;
                qtable.TextFileSemicolonDelimiter = false;
                qtable.TextFileCommaDelimiter = true;
                qtable.TextFileSpaceDelimiter = false;
                qtable.TextFileColumnDataTypes = columnDataTypes;

                setStatus("Starting export ..");
                qtable.Refresh(false);

                if (autoFitColumns == true)
                    qtable.Destination.EntireColumn.AutoFit();

                // cleanup
                qtable.Delete();
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
            finally
            {
                Enabled = true;
                setStatus("");
            }
        }

        public void Export(string fileName)
        {
            setStatus("Excel starting ...");
            Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application();

            Microsoft.Office.Interop.Excel.Workbook wb;
            int startRow = int.Parse(ctlStartCsv.Text);
            if (string.IsNullOrEmpty(ctlTemplate.Text))
                wb = excel.Workbooks.Add(Missing.Value);
            else
            {
                wb = excel.Workbooks.Add(ctlTemplate.Text);
                
            }
            Microsoft.Office.Interop.Excel.Worksheet sheet = wb.ActiveSheet;

            ImportCSV(fileName, sheet, sheet.Range["$" + ctlExcelCol.Text.ToUpperInvariant() + "$" + ctlExcelRow.Text],
                new int[] { 1 }, true, startRow);
            excel.Visible = true;
        }


        private void button1_Click(object sender, EventArgs e)
        {

            Export(ctlFileName.Text);




        }

        private void setStatus(string status)
        {
            ctlStatus.Text = status;
            Refresh();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            ofd.FileName = "*.csv";
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                ctlFileName.Text = ofd.FileName;
        }

        private void FmMain_Load(object sender, EventArgs e)
        {

        }

        private void ctlAssCsv_Click(object sender, EventArgs e)
        {
            FileAssociationInfo fai = new FileAssociationInfo(".csv");
            if (!fai.Exists)
            {
                fai.Create("KalitteCsvToExcel");

                fai.ContentType = "application/csv";

            }

            ProgramAssociationInfo pai = new ProgramAssociationInfo(fai.ProgID);


            var verb = pai.Verbs.SingleOrDefault(p => p.Name.ToLowerInvariant() == "open" && p.Command.StartsWith(System.Windows.Forms.Application.ExecutablePath));
            if (verb != null)
            {
                MessageBox.Show("Already registered. Registration Removed.");
                pai.RemoveVerb(verb);
            }
            else
            {
                ProgramVerb newVerb = new ProgramVerb("Open", System.Windows.Forms.Application.ExecutablePath + " \"%1\"");
                ProgramAssociationInfo newPai = pai.Create("Kalitte CSV to Excel Tool", newVerb);
                MessageBox.Show("Registered successfully.");
            }


        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            ofd.FileName = "*.xltm";
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                ctlTemplate.Text = ofd.FileName;
        }

        private void toolStripStatusLabel2_Click(object sender, EventArgs e)
        {
            Process.Start("http://www.dynamicdashboards.net");
        }
    }
}
